﻿
//系统日志   xu 2015.4.27
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using XQ.WeChatOfficialAccount.DAL.Model;
using XQ.WeChatOfficialAccount.DAL.Model.Sys;

namespace XQ.WeChatOfficialAccount.DAL.DAL.Common
{
    public class LogDal : BaseDal<Sys_logs>
    {
        // 
    }
    public class ErrLogDal : BaseDal<Sys_Errlogs>
    {
        //
    }
    public class LogBll
    {

         WeChatOfficialAccountEntities ce;

        /// <summary>
        /// 日志类型
        /// </summary>
        public enum LogType
        {
            AddNew = 1, //添加
            Update = 2, //更新
            Delete = 3, //删除
            SQL = 4,    //与数据库相关SQL信息           
            LogIn = 5,  //用户登录
            LogOut = 6,   //用户退出
            ChangePassword = 7,//更改密码 
            WrongPassword = 8,//多次密码错误
            Excel = 9, //导出Excel
            Search = 10, //查询
            Other = -1  //其他
        }

        private static readonly LogBll bll = new LogBll();
        public static LogBll Instance { get { return bll; } }

        /// <summary>
        /// 写操作日志
        /// </summary>
        /// <param name="vLogType">日志类型</param>
        /// <param name="vIPAddress">IP地址</param>
        /// <param name="vUserID">用户ID</param>
        /// <param name="vBusinessName">Business</param>
        /// <param name="vTableName">表名称</param>
        /// <param name="vPrimaryKey">记录ID</param>
        /// <param name="vSqlText">SQL</param>
        /// <param name="vDescription">日志内容</param>
        public void WriteLog(LogType vLogType,string vIPAddress, int vUserID,string vBusinessName,string vTableName, string vPrimaryKey, string vSqlText, string vDescription)
        {
            using (LogDal dal = new LogDal())
            {
                dal.AddModel(new Sys_logs() { OperationType = Convert.ToInt32(vLogType), OperationIp = vIPAddress, UserId = vUserID, BusinessName = vBusinessName, OperationTime = DateTime.Now, TableName = vTableName, PrimaryKey = vPrimaryKey, SqlText = vSqlText, Description = vDescription });
            }
        }
        /// <summary>
        /// 写错误日志
        /// </summary>
        /// <param name="vIPAddress">IP地址</param>
        /// <param name="vUserID">用户ID</param>
        /// <param name="vTableName">表名称</param>        
        /// <param name="vPrimaryKey">记录ID</param>
        /// <param name="vSqlText">SQL</param>
        /// <param name="vDescription">日志内容</param>
        /// <param name="vUrl">Url</param>
        /// <param name="vClassName">类名称</param>
        /// <param name="vFunctionName">过程名称</param>
        public void WriteErrLog(string vIPAddress, int vUserID, string vBusinessName, string vTableName, string vPrimaryKey, string vSqlText, string vDescription, string vUrl, string vClassName, string vFunctionName)
        {
            using (ErrLogDal dal = new ErrLogDal())
            {
                dal.AddModel(new Sys_Errlogs() { UserId = vUserID, OperationIp = vIPAddress, BusinessName = vBusinessName, TableName = vTableName, PrimaryKey = vPrimaryKey, SqlText = vSqlText, Description = vDescription, Url = vUrl, ClassName = vClassName, FunctionName = vFunctionName, CreateTime = DateTime.Now });
            }
        }

        /// <summary>
        ///获取操作日志数量
        /// </summary>
        /// <param name="vLogType">日志类型</param>
        /// <param name="vBFDate">期初</param>
        /// <param name="vEDDate">期末</param>
        /// <returns></returns>
        public int GetSys_logsNum(int vLogType, DateTime vBFDate, DateTime vEDDate)
        {
            try
            {
                using (ce = new WeChatOfficialAccountEntities())
                {
                    string sql = @" SELECT count(*) FROM Sys_logs AS A WHERE A.OperationTime>=@BFDate AND A.OperationTime<=@EDDate " + ((vLogType != 0) ? " AND A.OperationType=" + vLogType : "");
                    object[] par = { new SqlParameter("@BFDate", vBFDate), new SqlParameter("@EDDate", vEDDate) };
                    return ce.Database.SqlQuery<int>(sql, par).First();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取操作日志
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public List<SyslogModel> GetSys_logs(LogSearchModel searchModel, Pagination page)
        {
            try
            {
                using (ce = new WeChatOfficialAccountEntities())
                {
                    //分页
                    string pageSql = @" SELECT  1 FROM Sys_logs AS A WHERE A.OperationTime>=@BFDate AND A.OperationTime<=@EDDate " + ((searchModel.LogType != 0) ? " AND A.OperationType=" + searchModel.LogType : "");
                    object[] pagePar = { new SqlParameter("@BFDate", searchModel.StartTime), new SqlParameter("@EDDate", searchModel.EndTime), new SqlParameter("@PageSize", searchModel.pageSize), new SqlParameter("@Index", searchModel.index) };
                    int allCount = ce.Database.SqlQuery<int>(pageSql, pagePar).Count();
                    page.RecordCount = allCount;
                    page.PageCount = (int)Math.Ceiling((decimal)page.RecordCount / (decimal)searchModel.pageSize);
                    page.PageSize = searchModel.pageSize;
                    page.PageIndex = searchModel.index;

                    //查数据
                    string sql = @" SELECT TOP (@PageSize) * FROM (SELECT  ROW_NUMBER() OVER(order by A.id) as RowNum, A.ID, (select trueName from sys_users where id=a.userid) as UserName, A.OperationTime, A.TableName, A.BusinessName, A.PrimaryKey, A.SqlText, A.OperationIp, A.Description,CASE OperationType WHEN 1 THEN '添加记录' WHEN 2 THEN '编辑记录' WHEN 3 THEN '删除记录' WHEN 4 THEN 'SQL' WHEN 5 THEN '登录系统' WHEN 6 THEN '退出系统' WHEN 7 THEN '更改密码' WHEN 8 THEN '多次密码错误' WHEN - 1 THEN '其他' END AS 'OperationTypeName' FROM Sys_logs AS A WHERE A.OperationTime>=@BFDate AND A.OperationTime<=@EDDate " + ((searchModel.LogType != 0) ? " AND A.OperationType=" + searchModel.LogType : "") + " ) AS T where T.RowNum>@Index ";
                    searchModel.index = (searchModel.index - 1) * searchModel.pageSize;
                    object[] par = { new SqlParameter("@BFDate", searchModel.StartTime), new SqlParameter("@EDDate", searchModel.EndTime), new SqlParameter("@PageSize", searchModel.pageSize), new SqlParameter("@Index", searchModel.index) };
                    return ce.Database.SqlQuery<SyslogModel>(sql, par).ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        ///获取系统错误日志总数
        /// </summary> 
        /// <param name="vBFDate">期初</param>
        /// <param name="vEDDate">期末</param>
        /// <returns></returns>
        public int GetSys_ErrlogNum(DateTime vBFDate, DateTime vEDDate)
        {
            try
            {
                using (ce = new WeChatOfficialAccountEntities())
                {
                    string sql = @" SELECT count(a.id) FROM Sys_Errlogs AS A  WHERE A.CreateTime>=@BFDate AND CreateTime<=@EDDate ";

                    object[] par = { new SqlParameter("@BFDate", vBFDate), new SqlParameter("@EDDate", vEDDate) };
                    return ce.Database.SqlQuery<int>(sql, par).First();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取系统错误日志
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public List<SysErrlogModel> GetSys_Errlogs(LogSearchModel searchModel, Pagination page)
        {
            try
            {
                using (ce = new WeChatOfficialAccountEntities())
                {
                    //分页
                    string pageSql = @" SELECT  1 FROM Sys_Errlogs AS A WHERE A.CreateTime>=@BFDate AND CreateTime<=@EDDate";
                    object[] pagePar = { new SqlParameter("@BFDate", searchModel.StartTime), new SqlParameter("@EDDate", searchModel.EndTime), new SqlParameter("@PageSize", searchModel.pageSize), new SqlParameter("@Index", searchModel.index) };
                    int allCount = ce.Database.SqlQuery<int>(pageSql, pagePar).Count();
                    page.RecordCount = allCount;
                    page.PageCount = (int)Math.Ceiling((decimal)page.RecordCount / (decimal)searchModel.pageSize);
                    page.PageSize = searchModel.pageSize;
                    page.PageIndex = searchModel.index;

                    //查数据
                    string sql = @" SELECT TOP (@PageSize) * FROM (SELECT  ROW_NUMBER() OVER(order by A.id) as RowNum,A.ID, (select top 1 trueName from sys_users where id=a.userid) as UserName, A.TableName, A.BusinessName, A.PrimaryKey, A.SqlText, A.OperationIp, A.Url, A.ClassName, A.FunctionName, A.Description, A.CreateTime FROM Sys_Errlogs AS A WHERE A.CreateTime>=@BFDate AND CreateTime<=@EDDate  ) AS T where T.RowNum>@Index ";
                    searchModel.index = (searchModel.index - 1) * searchModel.pageSize;
                    object[] par = { new SqlParameter("@BFDate", searchModel.StartTime), new SqlParameter("@EDDate", searchModel.EndTime), new SqlParameter("@PageSize", searchModel.pageSize), new SqlParameter("@Index", searchModel.index) };
                    return ce.Database.SqlQuery<SysErrlogModel>(sql, par).ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



    }
}